﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using TrinityAPI.Base.Models;

namespace TrinityAPI.Base
{
    public abstract class DBBase : IDisposable
    {
        protected bool disposed;
        protected bool isTransaction;
        protected bool autoDispose = true;
        protected IsolationLevel? isolationLevel;
        protected DbConnection connection;
        protected DbTransaction transaction;
        protected string connectionString;
        protected string escapeFormat;
        protected DatabaseType databaseType;
        private Dictionary<string, string> operators;

        public DatabaseType DatabaseType
        {
            get { return databaseType; }
        }

        public string EscapeFormat
        {
            get
            {
                if (string.IsNullOrWhiteSpace(escapeFormat))
                    escapeFormat = "{0}";
                return escapeFormat;
            }
        }

        public Dictionary<string, string> Operators
        {
            get
            {
                if (operators == null)
                    operators = new Dictionary<string, string>()
                    {
                        { "=", "{0}={1}" },
                        { "!=", "{0}<>{1}" },
                        { ">", "{0}>{1}" },
                        { ">=", "{0}>={1}" },
                        { "<", "{0}<{1}" },
                        { "<=", "{0}<={1}" },
                        { "equals", "{0}={1}" },
                        { "notEqual", "{0}<>{1}" },
                        { "contains", "{0} like '%'+{1}+'%'" },
                        { "notContains", "{0} not like '%'+{1}+'%'" },
                        { "startsWith", "{0} like {1}+'%'" },
                        { "endsWith", "{0} like '%'+{1}" },
                        { "greaterThan", "{0}>{1}" },
                        { "greaterThanOrEqual", "{0}>={1}" },
                        { "lessThan", "{0}<{1}" },
                        { "lessThanOrEqual", "{0}<={1}" },
                        { "isNull", "{0} is null" },
                        { "isNotNull", "{0} is not null" }
                    };
                return operators;
            }
        }

        public DBBase(string connString)
        {
            this.connectionString = connString;
        }

        public DBBase(string connString, bool isTrans) : this(connString)
        {
            this.isTransaction = isTrans;
        }

        public DBBase(string connString, bool isTrans, IsolationLevel isolation) : this(connString, isTrans)
        {
            this.isolationLevel = isolation;
        }

        public DBBase(string connString, bool isTrans, bool autoDispose) : this(connString, isTrans)
        {
            this.autoDispose = autoDispose;
        }

        public DBBase(string connString, bool isTrans, bool autoDispose, IsolationLevel isolation) : this(connString, isTrans, isolation)
        {
            this.autoDispose = autoDispose;
        }

        ~DBBase()
        {
            this.Rollback();
        }

        protected void GetOutputs(DbParameterCollection Parameters, List<DBParameter> cmdParms)
        {
            foreach (DbParameter param in Parameters)
            {
                if (param.Direction == ParameterDirection.InputOutput || param.Direction == ParameterDirection.Output || param.Direction == ParameterDirection.ReturnValue)
                {
                    var cmdParm = cmdParms.Find(p => p.ParameterName == param.ParameterName);
                    if (cmdParm != null)
                    {
                        cmdParm.Value = param.Value;
                    }
                }
            }
        }

        public virtual void Dispose()
        {
            if (!this.disposed)
            {
                if (transaction != null)
                {
                    try
                    {
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        Rollback();
                    }
                    transaction.Dispose();
                    transaction = null;
                }
                if (connection != null)
                {
                    if (connection.State != ConnectionState.Closed)
                        connection.Close();
                    connection.Dispose();
                }
                this.disposed = true;
            }
        }

        public virtual void Rollback()
        {
            if (!this.disposed)
            {
                if (transaction != null)
                {
                    try
                    {
                        transaction.Rollback();
                    }
                    catch (Exception ex)
                    {
                    }
                    transaction.Dispose();
                    transaction = null;
                }
                if (connection != null)
                {
                    if (connection.State != ConnectionState.Closed)
                        connection.Close();
                    connection.Dispose();
                }
                this.disposed = true;
            }
        }

        public abstract string BuildPagingSql(string sql, string orderBy, int start, int end, bool isCount = true);
 
        public abstract DataSet Query(string cmdText, List<DBParameter> cmdParms = null, CommandType cmdType = CommandType.Text, int timeout = 30);

        public abstract object ExecuteScalar(string cmdText, List<DBParameter> cmdParms = null, CommandType cmdType = CommandType.Text, int timeout = 30);

        public abstract int ExecuteNonQuery(string cmdText, List<DBParameter> cmdParms = null, CommandType cmdType = CommandType.Text, int timeout = 30, bool hasOutput = false);
    }
}
